package com.best.oasis.settlement.util;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;

import org.apache.commons.lang.StringUtils;

import com.best.oasis.settlement.common.entity.base.BaseBizEntity;
import com.best.oasis.settlement.util.helper.JPAHelper;

public class GeneratorDDL {

    @SuppressWarnings("rawtypes")
	public static void main(String[] args) throws Exception {
        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
        System.out.println("请输入类的全路径包含类名（多个类用 ,隔开）：");
        String classNames = br.readLine();
        if (!classNames.isEmpty()) {
            classNames = classNames.trim();
            String[] classArray = classNames.split(",");
            for (String className : classArray) {
                Class clazz = Class.forName(className);
                table(clazz);
                seq(clazz);
                primaryKey(clazz);
                uniqueKey(clazz);
                
                // 生成mapper文件
                printMapper(clazz);
            }
        }
    }

    static class Model {
        public String column;

        public String prop;

        public String types;
    }

    @SuppressWarnings({"rawtypes" })
    private static String getTypesByField(Field field) {
        String res = "VARCHAR";
        Class type = field.getType();
        if (type == String.class) {
            res = "VARCHAR";
        } else if (type == Integer.class || type == Long.class || type == int.class || type == long.class || type == Double.class
                || type == double.class || type == Float.class || type == float.class) {
            res = "NUMERIC";
        } else if (type == Date.class) {
            res = "TIMESTAMP";
        } else if (type == Boolean.class || type == boolean.class) {
            res = "BIT";
        }
        return res;
    }

    @SuppressWarnings("rawtypes")
    private static List<Model> getColumnsByType(Class clazz) {
    	
    	List<Model> ret = new ArrayList<Model>();
    	
    	 String[] strs = {  
    			 			"id",   			"id",   			"NUMERIC",
							"code", 			"code", 			"VARCHAR",
							"createTime", 		"createTime", 		"TIMESTAMP", 
							"creator",   		"creator", 			"VARCHAR", 
							"creatorName", 		"creatorName", 		"VARCHAR", 
							"lastUpdateTime", 	"lastUpdateTime", 	"TIMESTAMP", 
							"lastUpdator",  	"lastUpdator", 		"VARCHAR",
							"lastUpdatorName", 	"lastUpdatorName", 	"VARCHAR" ,
							"optlock", 			"version", 			"NUMERIC" };
    	 
    	 boolean isInstanceOfBaseBizEntity = false;
		try {
			Object t = clazz.newInstance();
			if( t instanceof BaseBizEntity ){
				isInstanceOfBaseBizEntity = true;
	    	 }
		}  catch ( Exception e) {
			e.printStackTrace();
		}
    	 if( isInstanceOfBaseBizEntity ){
    		 for (int k = 0; k < strs.length; k = k + 3) {
    			 Model model = new Model();
    			 model.column = strs[k];
    			 model.prop = strs[k + 1];
    			 model.types = strs[k + 2];
    			 ret.add(model);
    		 }
    	 }

        String columName="";
        for (Field field : clazz.getDeclaredFields()) {
            if ("serialVersionUID".equalsIgnoreCase(field.getName())) {
                continue;
            }
            Model model = new Model();
            columName = field.getName();
            
            if (field.isAnnotationPresent(Column.class) && StringUtils.isNotEmpty(field.getAnnotation(Column.class).name())) {
            	columName = field.getAnnotation(Column.class).name().toLowerCase();
            } 
            model.column = columName;
            model.prop = field.getName();
            model.types = getTypesByField(field);
            ret.add(model);
        }

       
        return ret;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    private static void printMapper(Class clazz) {
        String tableName = JPAHelper.getTableName(clazz).toUpperCase();
        List<Model> list = getColumnsByType(clazz);
        String seqName = JPAHelper.getSequenceName(clazz);

        String typeName = clazz.getSimpleName();
        // typeAlias
        System.out.println("<typeAlias alias=\"" + typeName + "\" type=\"" + clazz.getName() + "\"/>");
        System.out.println("<typeAlias alias=\"" + typeName + "SO\" type=\"" + clazz.getName().replace("entity", "so") + "SO\"/>");
        System.out.println();

        String indentation1 = "    ";
        String indentation2 = "        ";
        String indentation3 = "            ";
        StringBuffer sb = new StringBuffer();
        sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + "\n");
        sb.append("<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" " + "\n");
        sb.append("\"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">" + "\n");
        sb.append("<mapper namespace=\"" + typeName + "\">" + "\n");
        sb.append("" + "\n");
       

       

        sb.append("" + "\n");
        sb.append(indentation1 + " <!-- get data from DB  start -->" + "\n");
        
        // getById
        sb.append(indentation1 + " <select id=\"getById\" parameterType=\"long\" resultType=\"" + typeName + "\">" + "\n");
        sb.append(indentation2 + " SELECT * FROM " + tableName + " WHERE ID = #{id}" + "\n");
        sb.append(indentation1 + " </select>" + "\n");
        sb.append("" + "\n");
        //getListByIds
        sb.append(indentation1 + " <select id=\"getListByIds\" parameterType=\"List\" resultType=\"" + typeName + "\">" + "\n");
        sb.append(indentation2 + " SELECT * FROM " + tableName + " t WHERE t.id in" + "\n");
        sb.append(indentation2 + " <foreach collection=\"list\" index=\"index\" item=\"item\" open=\"(\" close=\")\" separator=\",\">" + "\n");
        sb.append(indentation3 + " #{item}" + "\n");
        sb.append(indentation2 + " </foreach>" + "\n");
        sb.append(indentation1 + " </select> " + "\n");
        sb.append("" + "\n");
        
        // getAll
        sb.append(indentation1 + " <select id=\"getAll\" resultType=\"" + typeName + "\">" + "\n");
        sb.append(indentation2 + " SELECT * FROM " + tableName + " " + "\n");
        sb.append(indentation1 + " </select>" + "\n");
        sb.append("" + "\n");
        
        // getListByField
        sb.append(indentation1 + " <select id=\"getListByField\" resultType=\"" + typeName + "\">" + "\n");
        sb.append(indentation2 + " SELECT * FROM " + tableName + " WHERE ${fieldName} = #{colValue} " + "\n");
        sb.append(indentation1 + " </select>" + "\n");
        sb.append("" + "\n");
        
        //getListBySo
        sb.append(indentation1 + " <select id=\"getListBySo\" parameterType=\"" + typeName + "SO\" resultType=\"" + typeName + "\">" + "\n");
        sb.append(indentation2 + " SELECT t.* FROM " + tableName + " t " + "\n");
        sb.append(indentation2 + " <include refid=\"SO_Where_Clause\" />" + "\n");
        sb.append(indentation1 + " </select>" + "\n");
        sb.append("" + "\n");
        
        //getCountBySo
        sb.append(indentation1 + " <select id=\"getCountBySo\" parameterType=\"" + typeName + "SO\" resultType=\"long\">" + "\n");
        sb.append(indentation2 + " SELECT COUNT(t.id) FROM " + tableName + " t\n");
        sb.append(indentation2 + " <include refid=\"SO_Where_Clause\" />" + "\n");
        sb.append(indentation1 + " </select>" + "\n");
        sb.append("" + "\n");

        // SO_Where_Clause
        sb.append(indentation1 + " <sql id=\"SO_Where_Clause\">" + "\n");
        sb.append(indentation2 + " <where>" + "\n");
        sb.append(indentation2 + " 1 = 1 " + "\n");
        sb.append(indentation3 + " <if test = \"name != null and name!=''\">" + "\n");
        sb.append(indentation3 + "  and name LIKE '%'||#{name}||'%' " + "\n");
        sb.append(indentation3 + " </if>" + "\n");
        sb.append(indentation2 + " </where>" + "\n");
        sb.append(indentation2 + " <!-- <include refid=\"Base.Order_By_Clause\" /> -->" + "\n");
        sb.append(indentation1 + " </sql>" + "\n");
        
        sb.append(indentation1 + " <!-- get data from DB  end -->" + "\n");
        sb.append("" + "\n");
        
        sb.append(indentation1 + " <!-- insert data into DB  start-->" + "\n");
        // insert
        sb.append(indentation1 + " <insert id=\"insert\" parameterType=\"" + typeName + "\">" + "\n");
        sb.append(indentation2 + " <selectKey keyProperty=\"id\" resultType=\"long\" order=\"BEFORE\">" + "\n");
        sb.append(indentation3 + "<include refid=\"sqlmap.common.getIdSequenceSql\"/>" + "\n");
        sb.append(indentation2 + " </selectKey>" + "\n");

        sb.append(indentation2 + " INSERT INTO " + tableName + "(\n");
        for (int k = 0; k < list.size(); k++) {
            Model model = list.get(k);
            if (k == list.size() - 1) {
                sb.append(indentation2 + " <!-- " + k + " --> " + model.column + "\n");
            } else {
                sb.append(indentation2 + " <!-- " + k + " --> " + model.column + ",\n");
            }
        }
        sb.append(indentation2 + ")\n");
        sb.append(indentation2 + " VALUES( \n");
        for (int k = 0; k < list.size(); k++) {
            Model model = list.get(k);
            if (k == list.size() - 1) {
                sb.append(indentation2 + " <!-- " + k + " --> #{ " + model.prop + ":" + model.types + " }\n");
            } else {
                sb.append(indentation2 + " <!-- " + k + " --> #{ " + model.prop + ":" + model.types + " },\n");
            }
        }
        sb.append(indentation2 + ")\n");
        sb.append(indentation1 + "</insert>" + "\n");
        sb.append("" + "\n");
        
        sb.append(indentation1 + "<!-- insert data into DB  end -->" + "\n");
        sb.append("" + "\n");
        
        sb.append(indentation1 + "<!-- update data start -->" + "\n");
        // update
        sb.append(indentation1 + " <update id=\"update\" parameterType=\"" + typeName + "\">" + "\n");
        sb.append(indentation2 + "  UPDATE " + tableName + " SET \n");
        for (int k = 0; k < list.size(); k++) {
            Model model = list.get(k);
            if (!model.column.equals("optlock") && !model.column.equals("id")) {
                sb.append(indentation3 + " " + model.column + " = #{ " + model.prop + ":" + model.types + " },\n");
            }
        }
        sb.append(indentation3 + " optlock = optlock + 1 \n");
        sb.append(indentation2 + " WHERE \n");
        sb.append(indentation3 + "     ID = #{id} \n");
        sb.append(indentation3 + " AND OPTLOCK = #{version} \n");
        sb.append(indentation1 + " </update>" + "\n");
        sb.append("" + "\n");
        
        sb.append(indentation1 + " <!-- update data end -->" + "\n");
        sb.append("" + "\n");
        sb.append(indentation1 + " <!-- delete data start -->" + "\n");
        sb.append("" + "\n");
        //deleteById
        sb.append(indentation1 + " <delete id=\"deleteById\" parameterType=\"long\">" + "\n");
        sb.append(indentation2 + "  DELETE FROM " + tableName + " WHERE ID = #{id} " + "\n");
        sb.append(indentation1 + " </delete>" + "\n");
        sb.append("" + "\n");
        
        //deleteByIds
        sb.append(indentation1 + " <delete id=\"deleteByIds\" parameterType=\"List\">" + "\n");
        sb.append(indentation2 + "  DELETE FROM " + tableName + " t WHERE t.id IN " + "\n");
        
        sb.append(indentation3 + " <foreach collection=\"list\" index=\"index\" item=\"item\" open=\"(\" close=\")\" separator=\",\">" + "\n");
        sb.append(indentation3 + "   #{item} " + "\n");
        sb.append(indentation3 + " </foreach>" + "\n");
        
        sb.append(indentation1 + " </delete>" + "\n");
        sb.append("" + "\n");
        
        sb.append(indentation1 + "<!-- delete data end -->" + "\n");
        sb.append("" + "\n");
        
        sb.append("</mapper>" + "\n");
        System.out.println(sb.toString());
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    private static String table(Class clazz) {
        StringBuffer sql = new StringBuffer();
        if (clazz.isAnnotationPresent(Table.class)) {
            // 该class存在Table类型的注解，获取指定的表名
            Table table = (Table) clazz.getAnnotation(Table.class);
            String tableName = table.name();
            sql.append("--create table " + tableName + "\n");
            sql.append("create table " + tableName + " (");
        }
        Field[] fArr = clazz.getDeclaredFields();
        List<String> columnList = getColumns(fArr);
        // 拼接解析后的成员变量信息成创建表语句
        for (int i = 0; i < columnList.size(); i++) {
            if (i == (columnList.size() - 1)) {
                sql.append("\n" + columnList.get(i) + ")");
            } else {
                sql.append("\n" + columnList.get(i) + ",");
            }
        }
        sql.append(";");
        sql.append("\n");
        System.out.println(sql.toString());
        return sql.toString();
    }

    /**
     * 用来解析所有成员变量的方法
     */
    @SuppressWarnings("rawtypes")
	public static List<String> getColumns(Field[] fArr) {
        List<String> result = new ArrayList<String>();
        
        result.add("ID NUMBER(19) NOT NULL ");
        result.add("CODE VARCHAR2(100 CHAR) ");
        result.add("CREATETIME TIMESTAMP(6) ");
        result.add("CREATOR VARCHAR2(50 CHAR) ");
        result.add("CREATORNAME VARCHAR2(100 CHAR) ");
        
        result.add("LASTUPDATETIME TIMESTAMP(6) ");
        result.add("LASTUPDATOR VARCHAR2(50 CHAR) ");
        result.add("LASTUPDATORNAME VARCHAR2(100 CHAR) ");
        result.add("optlock NUMBER(10) default 0 ");
        
        
        String columnName = "";
        String columnLength = "";
        String columnType = "";
        for (int i = 0; i < fArr.length; i++) {
            Field f = fArr[i];
            columnName = f.getName();
            if ("serialVersionUID".equalsIgnoreCase(columnName)) {
                continue;
            }
            boolean haveLength = true;
            if (f.isAnnotationPresent(Id.class)) {
                // columnName = f.getName();
                columnLength = "19";
                String str = columnName + " number" + "(" + columnLength + ")";
                result.add(str);
            } else {
                if (f.isAnnotationPresent(Column.class)) {
                    columnName = f.getAnnotation(Column.class).name();
                }/*
                  * else{ columnName = f.getName(); }
                  */
                Class type = f.getType();
                if (Integer.class == type || Long.class == type) {
                    columnLength = "19";
                    columnType = "number";
                } else if (Float.class == type || Double.class == type) {
                    columnLength = "19,4";
                    columnType = "number";
                } else if (Boolean.class == type) {
                    columnLength = "1";
                    columnType = "number";
                } else if (Date.class == type) {
                    if (f.isAnnotationPresent(Temporal.class)) {
                        if ("TIMESTAMP".equalsIgnoreCase(f.getAnnotation(Temporal.class).value().toString())) {
                            columnLength = "6";
                            columnType = "timestamp";
                        } else if ("DATE".equalsIgnoreCase(f.getAnnotation(Temporal.class).value().toString())) {
                            columnType = "DATE";
                            haveLength = false;
                        }
                    } else {
                        haveLength = false;
                        columnType = "DATE";
                    }

                } else {
                    columnLength = "255";
                    columnType = "varchar2";
                }
                String strColumnLength = "";
                if (haveLength) {
                    strColumnLength = " ( " + columnLength + " ) ";
                }
                String str = columnName + " " + columnType + strColumnLength;
                result.add(str);
            }
        }
        
        
        //result.add("RE_MARK VARCHAR2(1024)");
        return result;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    private static String seq(Class clazz) {
        StringBuffer sequence = new StringBuffer();
        if (clazz.isAnnotationPresent(SequenceGenerator.class)) {
            // 该class存在Table类型的注解，获取指定的表名
            SequenceGenerator seq = (SequenceGenerator) clazz.getAnnotation(SequenceGenerator.class);
            String seqName = seq.name();
            sequence.append("--create sequence " + seqName + "\n");
            sequence.append("create sequence " + seqName);
            sequence.append(" minvalue 1 ");
            sequence.append(" maxvalue 999999999999999999999999999 ");
            sequence.append(" start with 60001 ");
            sequence.append(" increment by 1 ");
            sequence.append(" cache 20; ");
            sequence.append("\n");
        }
        System.out.println(sequence.toString());
        return sequence.toString();
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    private static String primaryKey(Class clazz) {
        StringBuffer key = new StringBuffer();

        if (clazz.isAnnotationPresent(Table.class)) {
            // 该class存在Table类型的注解，获取指定的表名
            Table table = (Table) clazz.getAnnotation(Table.class);
            String tableName = table.name();
            key.append("--create key:ID " + "\n");
            key.append("alter table " + tableName + " add primary key (ID);");
            key.append("\n");
        }
        System.out.println(key.toString());
        return key.toString();
    }
    @SuppressWarnings({ "unchecked", "rawtypes" })
	private static String uniqueKey(Class clazz) {
        StringBuffer key = new StringBuffer();

        if (clazz.isAnnotationPresent(Table.class)) {
            // 该class存在Table类型的注解，获取指定的表名
            Table table = (Table) clazz.getAnnotation(Table.class);
            String tableName = table.name();
            key.append("--create unique key:CODE " + "\n");
            key.append("alter table " + tableName + " add unique (CODE);");
            key.append("\n");
        }
        System.out.println(key.toString());
        return key.toString();
    }
}
